package com.lemurian.bankdata.bean;

import java.util.Date;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.lemurian.bankdata.report.dto.PencocokanTransaksi;
import com.lemurian.entity.SignatureSchedule;

@Repository
public class ReportDAO {
	
	@Autowired private SessionFactory sessionFactory;
	
	public Session getSession() {
		return sessionFactory.getCurrentSession();
	}

	public List<PencocokanTransaksi> listPencocokanTransaksi(String orderBy, String ascDesc, String namaDebitur, String accountOfficer, Date lo, Date hi) {
		List<PencocokanTransaksi> list = null;
		
		try {
			Criteria criteria = initilizeCriteriaPencocokanTrx(orderBy, ascDesc, namaDebitur, accountOfficer, lo, hi);
			
			criteria.setResultTransformer(Transformers.aliasToBean(PencocokanTransaksi.class));
			
			list = criteria.list();
		} catch (Throwable t) {
			t.printStackTrace();
		}
		
		return list;
	}
	
	public Criteria initilizeCriteriaPencocokanTrx(String orderBy, String ascDesc, String namaDebitur, String accountOfficer, Date lo, Date hi) throws Exception {
		
		Criteria criteria = getSession().createCriteria(SignatureSchedule.class);
		
		criteria.setProjection(initializeProjectionPencocokanTrx());
		
		criteria.setCacheable(true);
		
		criteria.createCriteria("client", "client", Criteria.LEFT_JOIN);
		
		criteria.createCriteria("client.debitur", "debitur", Criteria.LEFT_JOIN);
		
		criteria.createCriteria("client.clientCertificates", "clientCertificates", Criteria.LEFT_JOIN);
		
		criteria.createCriteria("client.accountOfficer", "accountOfficer", Criteria.LEFT_JOIN);
		
		criteria.createCriteria("clientCertificates.certificateType", "certificateType", Criteria.LEFT_JOIN);
		
		criteria.add(Restrictions.between("signatureDate", lo, hi));
		
		if(namaDebitur != null && namaDebitur.length() > 0) {
			criteria.add(Restrictions.ilike("debitur.name", namaDebitur));
		}
		
		if(accountOfficer != null && accountOfficer.length() > 0) {
			criteria.add(Restrictions.ilike("accountOfficer.name", accountOfficer));
		}
		
		if (ascDesc.equalsIgnoreCase("desc")) {
			criteria.addOrder(Order.desc(orderBy.length() > 0 ? orderBy : "id"));
		} else {
			criteria.addOrder(Order.asc(orderBy.length() > 0 ? orderBy : "id"));
		}
		
		return criteria;
	}
	
	public ProjectionList initializeProjectionPencocokanTrx() {
		ProjectionList projection = Projections.projectionList();
		
		projection.add(Projections.property("client.id").as("noForm"));
		projection.add(Projections.property("debitur.name").as("debitur"));
		//projection.add(Projections.property("certificate.name").as("namaSertifikat"));
		//projection.add(Projections.property("certificateType.name").as("jenisAkta"));
		//projection.add(Projections.property("certificate.reqion").as("wilayah"));
		//projection.add(Projections.property("certificate.name").as("klien"));
		projection.add(Projections.property("accountOfficer.name").as("accountOfficer"));
		projection.add(Projections.property("client.tbo").as("tboDokumen"));
		
		projection.add(Projections.groupProperty("client.id"));
		projection.add(Projections.groupProperty("debitur.name"));
		//projection.add(Projections.groupProperty("certificate.name"));
		//projection.add(Projections.groupProperty("certificateType.name"));
		//projection.add(Projections.groupProperty("certificate.reqion"));
		projection.add(Projections.groupProperty("accountOfficer.name"));
		projection.add(Projections.groupProperty("client.tbo"));
		
		return projection;
	}
}